﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using CMS.Core.Libs;

namespace _4T.DAL
{
    public class PersonalInfoDAL
    {
        public PersonalInfoDAL() { }

        public int ValidatePerson(string username, string password)
        {
            int returnValue = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoLogin";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = password;
                    cmd.Connection = conn;
                    returnValue = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return returnValue;
        }

        public PersonalInfo GetPersonalInfo(string personalCode)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                PersonalInfo person = null;
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoGet";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        person = GetPersonFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return person;
            }

            return null;
        }

        public PersonalInfo GetPersonalInfo(string username, string password)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();

                PersonalInfo person = null;
                SqlDataReader reader = null;
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoGetByUsernameAndPasword";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = password;
                    cmd.Connection = conn;

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        person = GetPersonFromReader(reader);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return person;
            }

            return null;
        }

        public List<PersonalInfo> GetPersonalListInRole(int role)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter da;
                DataTable dt;
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoGetListByRole";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Role", SqlDbType.Int).Value = role;
                    cmd.Connection = conn;
                    dt = new DataTable();
                    da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<PersonalInfo> list = new List<PersonalInfo>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        PersonalInfo person = GetPersonalInfoFromDataRow(dr);
                        list.Add(person);
                    }

                    return list;
                }
            }
            return null;
        }

        public List<PersonalInfo> GetPersonalListInSpecificRole(int role)
        {
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter da;
                DataTable dt;
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoGetListBySpecificRole";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Role", SqlDbType.Int).Value = role;
                    cmd.Connection = conn;
                    dt = new DataTable();
                    da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<PersonalInfo> list = new List<PersonalInfo>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        PersonalInfo person = GetPersonalInfoFromDataRow(dr);
                        list.Add(person);
                    }

                    return list;
                }
            }
            return null;
        }

        private PersonalInfo GetPersonalInfoFromDataRow(DataRow dr)
        {
            string personalCode = string.Empty;
            if (dr[0] != null) personalCode = Convert.ToString(dr[0]);

            string username = string.Empty;
            if (dr[1] != null) username = Convert.ToString(dr[1]);

            string fullName = string.Empty;
            if (dr[2] != null) fullName = Convert.ToString(dr[2]);

            string phoneNumber = string.Empty;
            if (dr[3] != null) phoneNumber = Convert.ToString(dr[3]);

            string email = string.Empty;
            if (dr[4] != null) email = Convert.ToString(dr[4]);

            int role = 0;
            if (dr[5] != null) role = Convert.ToInt32(dr[5]);

            string rights = string.Empty;
            if (dr[6] != null) rights = Convert.ToString(dr[6]);

            return new PersonalInfo(personalCode, username, fullName, phoneNumber, email, role, rights);
        }

        private PersonalInfo GetPersonFromReader(SqlDataReader reader)
        {
            string personalCode = reader.GetString(0);

            string username = string.Empty;
            if (reader.GetValue(1) != DBNull.Value)
                username = reader.GetString(1);

            string fullName = string.Empty;
            if (reader.GetValue(2) != DBNull.Value)
                fullName = reader.GetString(2);

            string phoneNumber = string.Empty;
            if (reader.GetValue(3) != DBNull.Value)
                phoneNumber = reader.GetString(3);

            string email = string.Empty;
            if (reader.GetValue(4) != DBNull.Value)
                email = reader.GetString(4);

            int role = 0;
            if (reader.GetValue(5) != DBNull.Value)
                role = reader.GetInt32(5);

            string rights = string.Empty;
            if (reader.GetValue(6) != DBNull.Value)
                rights = reader.GetString(6);

            return new PersonalInfo(personalCode, username, fullName, phoneNumber, email, role, rights);
        }

        public int InsertPersonalInfo(string personalCode, string username, string password, string fullName,
            string phoneNumber, string email, string addr, string desc, string type, int role, string rights)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = password;
                    cmd.Parameters.Add("@FullName", SqlDbType.NText).Value = fullName;
                    cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = phoneNumber;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = email;
                    cmd.Parameters.Add("@Address", SqlDbType.NText).Value = addr;
                    cmd.Parameters.Add("@Description", SqlDbType.NText).Value = desc;
                    cmd.Parameters.Add("@PersonalType", SqlDbType.VarChar, 50).Value = type;
                    cmd.Parameters.Add("@Role", SqlDbType.Int).Value = role;
                    cmd.Parameters.Add("@Rights", SqlDbType.VarChar, 20).Value = rights;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public int UpdatePersonalInfo(string personalCode, string username, string password, string fullName,
            string phoneNumber, string email, string addr, string desc, int role, string rights)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "PersonalInfoUpdate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@PersonalCode", SqlDbType.VarChar, 50).Value = personalCode;
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = password;
                    cmd.Parameters.Add("@FullName", SqlDbType.NText).Value = fullName;
                    cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = phoneNumber;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = email;
                    cmd.Parameters.Add("@Address", SqlDbType.NText).Value = addr;
                    cmd.Parameters.Add("@Description", SqlDbType.NText).Value = desc;
                    cmd.Parameters.Add("@Role", SqlDbType.Int).Value = role;
                    cmd.Parameters.Add("@Rights", SqlDbType.VarChar, 20).Value = rights;
                    cmd.Connection = conn;

                    result = Convert.ToInt32(cmd.ExecuteScalar());

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }

            return result;
        }

        public IEnumerable<ProfileVerifierCus> GetAllProfileByVerifier(string verifier)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "ProfileGetListForVerify";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Verifier", SqlDbType.VarChar).Value = verifier;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new ProfileVerifierCus
                {
                    Verifier = n["Verifier"] is DBNull ? string.Empty : n["Verifier"].ToString(),
                    VerifyDate = n["VerifyDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["VerifyDate"]),
                    ProfileName = n["ProfileName"] is DBNull ? string.Empty : n["ProfileName"].ToString()
                });
            }
            else
            {
                return new List<ProfileVerifierCus>();
            }
        }

        public IEnumerable<PersonalInfo> GetAllPersonalInfo()
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetAllPersonalInfo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new PersonalInfo
                {
                    PersonalCode = n["PersonalCode"] is DBNull ? string.Empty : n["PersonalCode"].ToString(),
                    FullName = n["FullName"] is DBNull ? string.Empty : n["FullName"].ToString(),
                    PhoneNumber = n["PhoneNumber"] is DBNull ? string.Empty : n["PhoneNumber"].ToString(),
                    Email = n["Email"] is DBNull ? string.Empty : n["Email"].ToString()
                });
            }
            else
            {
                return new List<PersonalInfo>();
            }
        }
    }
}